﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YunSpace.Data;
using YunSpace.Domain.ViewModel.OrderFlowManage;
using YunSpace.Domain.ViewModel.WeChat.Site;
using YunSpace.Domain.ViewModel.WeChat;
using YunSpace.Core;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace YunSpace.Application.WeChat
{
    public class SiteApp
    {

        /// <summary>
        /// 获取场地列表信息
        /// </summary>
        /// <returns></returns>
        public List<WeChatSiteGridDto> GetSiteList(int from, int limit, string sort, string by, string keyword, string cityId, string position, string sitetype, string peoples, string prices)
        {

            //var queryParam = queryJson.ToJObject();

            using (var db = new RepositoryBase())
            {
                var sqlStr = $@"SELECT s.F_Id Id,
                                         s.site_type SiteType,
                                         s.city_id CityId, 
                                         s.district_id DistrictId, 
                                         s.title Title,
                                         photo.ImageSrc,
                                         s.address Address,
                                         s.subway_distance SubwayDistance,      
                                         s.ident Ident,
                                         s.parking_space ParkingSpace,
                                         sp.max_seating_capacity MaxCapacity,
                                         sp.F_YunPrice as YunPrice,
                                         sp.F_MarketPrice as MarketPrice,
                                         sp.F_Units Units,
                                         sp.height Height,
                                         sp.area Area,
                                         s.F_CreatorTime CreateTime,
                                         (SELECT F_FullName FROM Sys_Area sa WHERE sa.F_Id=s.city_id) CityName,
                                         (SELECT F_FullName FROM Sys_Area sa WHERE sa.F_Id = s.district_id ) District,
                                         (SELECT F_FullName FROM Sys_Area sa WHERE sa.F_Id = s.business_area ) BusinessArea,
                                         (select detail.F_ItemName from Sys_ItemsDetail detail inner join Sys_Items item on detail.F_ItemId = item.F_Id where item.F_EnCode='SiteType' and detail.F_ItemCode= s.site_type) SiteTypeName,
                                         (select detail.F_ItemName from Sys_ItemsDetail detail inner join Sys_Items item on detail.F_ItemId = item.F_Id where item.F_EnCode='units' and detail.F_ItemCode= sp.F_Units) UnitsName
                                    from SL_Site s 
                                    inner join (select sp.F_path ImageSrc, sp.photoable_id from SL_Photos sp where sp.photoable_type='Site' and sp.is_cover = 1 and sp.F_DeleteMark!=1 group by sp.photoable_id) photo on s.F_id = photo.photoable_id 
                                    inner join (select min(sc.F_MarketPrice) as F_MarketPrice,sc.max_seating_capacity,sc.F_YunPrice,sc.F_Units,F_SiteId, sc.height, sc.area from SL_Space sc  where sc.state=1 group by F_SiteId) sp 
                                on s.f_id=sp.F_SiteId
                                where s.state=1";

                if (!keyword.IsEmpty())
                {
                    sqlStr = $"{ sqlStr } and s.title like '%{keyword}%'";
                }
                if (!cityId.IsEmpty())
                {
                    sqlStr = $"{ sqlStr } and s.city_id = {cityId}";
                }
                if (!sitetype.IsEmpty())
                {
                    if (sitetype.Contains(','))
                    {
                        sqlStr = $"{ sqlStr } and s.site_type in({sitetype})";
                    }
                    else {
                        sqlStr = $"{ sqlStr } and s.site_type = {sitetype}";
                    }
                    
                }
                if (!position.IsEmpty() && position != "1")
                {
                    sqlStr = $"{ sqlStr } and s.district_id = {position}";
                }
                if (!peoples.IsEmpty())
                {
                    var ps = peoples.Split('-');
                    if (ps.Length == 1)
                    {
                        sqlStr = $"{ sqlStr } and sp.max_seating_capacity >= {ps.FirstOrDefault()}";
                    }
                    else if (ps.Length == 2)
                    {
                        sqlStr = $"{ sqlStr } and sp.max_seating_capacity >= {ps.FirstOrDefault()} and sp.max_seating_capacity <= {ps.LastOrDefault()}";
                    }
                }

                if (!prices.IsEmpty())
                {
                    var prs = prices.Split('~');
                    if (prs.Length == 2)
                    {
                        sqlStr = $"{ sqlStr } and sp.F_YunPrice >= {prs.FirstOrDefault()} and sp.F_YunPrice <= {prs.LastOrDefault()}";
                    }
                }

                sqlStr = $"{ sqlStr } order by { by } { sort } limit {from},{limit}";

                var result = db.FindList<WeChatSiteGridDto>(sqlStr);
                return result;
            }
        }


        /// <summary>
        /// 获取同类型场地
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public List<WeChatSiteGridDto> GetHomeLikeSitesList(string keyValue)
        {
            using (var db = new RepositoryBase())
            {
                string strSql = @"SELECT s.F_Id Id,
                                      s.site_type SiteType,
                                      s.city_id CityId, 
                                      s.district_id DistrictId, 
                                      s.title Title,
                                      photo.ImageSrc,
                                      s.address Address,
                                      s.subway_distance SubwayDistance,      
                                      s.ident Ident,
                                      s.parking_space ParkingSpace,
                                      sp.max_seating_capacity MaxCapacity,
                                      sp.F_YunPrice as YunPrice,
                                      sp.F_MarketPrice as MarketPrice,
                                      sp.F_Units Units,
                                      sp.height Height,
                                      sp.area Area,
                                      s.F_CreatorTime CreateTime,
                                      (SELECT F_FullName FROM Sys_Area sa WHERE sa.F_Id=s.city_id) CityName,
                                      (SELECT F_FullName FROM Sys_Area sa WHERE sa.F_Id = s.district_id ) District,
                                      (SELECT F_FullName FROM Sys_Area sa WHERE sa.F_Id = s.business_area ) BusinessArea,
                                      (select detail.F_ItemName from Sys_ItemsDetail detail inner join Sys_Items item on detail.F_ItemId = item.F_Id where item.F_EnCode='SiteType' and detail.F_ItemCode= s.site_type) SiteTypeName,
                                      (select detail.F_ItemName from Sys_ItemsDetail detail inner join Sys_Items item on detail.F_ItemId = item.F_Id where item.F_EnCode='units' and detail.F_ItemCode= sp.F_Units) UnitsName
                                from SL_Site s 
                                inner join (select sp.F_path ImageSrc, sp.photoable_id from SL_Photos sp where sp.photoable_type='Site' and sp.is_cover = 1 and sp.F_DeleteMark!=1 group by sp.photoable_id) photo on s.F_id = photo.photoable_id 
                                inner join (select min(sc.F_MarketPrice) as F_MarketPrice,sc.max_seating_capacity,sc.F_YunPrice ,sc.F_Units,F_SiteId, sc.height, sc.area from SL_Space sc where sc.state=1 group by F_SiteId) sp 
                                on s.f_id=sp.F_SiteId
                                where s.state=1 and s.F_id in(" + keyValue + ")";
                return db.FindList<WeChatSiteGridDto>(strSql);
                //where s.state=1 and s.F_id in(" + keyValue +") order by rand()  limit 5";

                //DbParameter[] parameter =
                //{
                //    new MySqlParameter("@fId", keyValue)
                //};
                //return db.FindList<WeChatSiteGridDto>(strSql, parameter);
            }
        }
    }
}
